#!/usr/bin/env python
# coding=utf-8
import pandas as pd
import numpy as np
import time
from include import dataBase

class dealPool:
    def __init__(self,connect):
        self.connect = connect

    def save(self,poolName,code,date):
        ''' 用于各种池类数据的存储  
            poolName : str 
            code : list [code]
            date : str '''
        # 建表
        tableName = '''{}{}'''.format(poolName,date)
        sql = ''' CREATE TABLE IF NOT EXISTS `{}` (code VARCHAR(10) PRIMARY KEY);'''.format(tableName)
        self.connect.create(tableName,sql)
        # 插入数据
        # 如果当天池为空，则直接返回
        if len(code) == 0:
            return
        for rowCode in code:
            sql =  ''' insert into `{}` values ('{}') '''.format(tableName,rowCode)
            self.connect.insert(tableName,sql)
        self.connect.commit
        print(tableName + "更新成功")

    def getTopLimit(self,date):
        ''' 获取当天涨停的股票
            date: str'''
        sql = ''' select * from `{}` where isTop >= 1; '''.format(date + "test")
        result = self.connect.select(date + "test",sql)
        topCode = list(zip(*result))[0]
        self.save("topPoolTest",topCode,date)

    def getBottom(self,date):
        ''' 获取当天大跌的股票
            date: str'''
        sql = ''' select * from `{}` where closeChange < -7; '''.format(date + "test")
        result = self.connect.select(date + "test",sql)
        downCode = list(zip(*result))[0]
        self.save("downPoolTest",downCode,date)

    def getMeat(self,date):
        ''' 获取当天大肉的股票
            date: str'''
        sql = ''' select * from `{}` where maxProfit > 7 and isTop = 0; '''.format(date + "test")
        result = self.connect.select(date + "test",sql)
        meatCode = list(zip(*result))[0]
        self.save("meatPoolTest",meatCode,date)

    def getNoodle(self,date):
        ''' 获取当天大面的股票
            date: str'''
        sql = ''' select * from `{}` where maxLoss > 7 and closeChange > -9; '''.format(date + "test")
        result = self.connect.select(date + "test",sql)
        noodleCode = list(zip(*result))[0]
        self.save("noodlePoolTest",noodleCode,date)

    def getOpenPool(self,date,code):
        ''' 查询一系列代码的开盘价并返回
            date : str 
            code : list[str]        '''
        if len(code) == 0:
            return 0;
        if len(code) == 1:
            sql = ''' select avg(openChange) from `{}` where stockCode in ('{}') '''.format(date,code[0])
        else:
            sql = ''' select avg(openChange) from `{}` where stockCode in {} '''.format(date,code)
        return round(self.connect.select(date,sql)[0][0],2)








